Excel Input Editor

The Excel Input Editor allows you to select the named range of cells in a MS Excel worksheet to which a PetroVR input will be linked, and to define the properties with which its values will be imported into the model. It opens every time you define a PetroVR input as linked. It can also be accessed by invoking the Properties of a variable already defined as an Excel link or by clicking the Excel Input icon next to it.

  • Excel file: Select the worksheet from which you want the project to read values. The buttons on the right allow you to:
    • Browse Browse for a file.
    • Refresh Refresh the list of ranges in the selected file. Use this when editing the worksheet and the PetroVR model simultaneously.
    • Open worksheet Open the selected file in MS Excel.
The dropdown list in the file selection box shows all the Excel files used so far in the current model, followed by all recent files as shown in the Excel File menu.
  • Range: Select the named range of cells to be linked. For a group of cells to be able to be linked from PetroVR they must have been defined as an Excel range, i.e. they must be associated to a unique name. This holds true both for single-value variables (which reference just one cell), arrays and tables. To the left, use the dropdown menu to select the unit in which the value retrieved from Excel will be interpreted.
  • Show only ranges defined in sheet: Filter ranges by existing sheets in the worksheet.

PetroVR supports linking to multi-area Excel ranges (i.e. non contiguous areas or groups of cells), such as a selection of columns of a larger table, provided the range contains aligned columns of the same size without skipping rows. Note that PetroVR follows the natural order of columns in the Excel spreadsheet, even though Excel provides the list of areas in the sequence entered by the user. For instance, if you define the range as C1:C4 , A1:A4, PetroVR will read it as if it was defined A1:A1 , C1:C4 because that is how it appears on the screen.

A range can only be linked to a PetroVR variable if it has the same number of rows and columns as the linked variable:

  • Single-value variables must reference ranges that contain one cell.
  • Array variables must be linked to horizontal ranges (i.e., contiguous cells belonging to same row) that comprise as many cells as reporting periods there are in the project (see Single-Value, Array and Table Variables). If the range contains cells from more than one row PetroVR will interpret the data as a table instead, even if they are in a single column. If the variable in question is a user-defined variable it will be converted to the table type; if it is a system variable, a validation warning will be issued.
  • User-defined tables must be linked to ranges with any number of rows and columns, since the PetroVR table will adjust to the size of the linked range (deleting or adding rows/columns when necessary). Decline Tables and Well Rate Table can be linked to ranges with exactly the same number of columns require by each, but will adjust the number of rows.
  • It is possible to link any variable to a dynamic Excel range, that is, one defined in Excel as a function, whose content or size may vary. In this case PetroVR will also update its reference, provided the above conditions are still met (i.e. single-value variables pointing to single cells, array variables pointing to ranges with as many columns as reporting periods, etc.)

The properties you are required to provide in order to let PetroVR correctly read and convert the Excel data vary depending on the type of input you are linking. For single-value and array variables the following is required:

  • Excel unit: Unit in which the values in the Excel range will be interpreted. (Keep in mind that PetroVR disregards format and unit information associated with the Excel cells and retrieves only the numeric value.) This will be limited by the Unit type selected below.
  • Description
  • Unit type: Unit type for the retrieved value. This will determine both the available units selectable for the Excel variable above, and the one for the PetroVR unit below.
  • Unit: Unit of the linked PetroVR variable.
  • Decimals: Number of decimals to be displayed.

The Value resulting from the link is shown below; this will be the value of the variable in the PetroVR model. For array-type variables, only the value of the first cell is displayed.

When a PetroVR variable is linked to an Excel range, it is important to remember that the unit type must be the same for both, although the units themselves may be different. For example, your PetroVR variable may be expressed in bbl and the value retrieved from Excel may be interpreted as m3. Every time you change the unit type in PetroVR, the system forces the Excel value to adopt the same unit type, and guesses that the one you used in PetroVR is the same that it has to use for the Excel value.

Note that PetroVR retrieves only the numeric value of the Excel range, and that the unit selected here for reading its value overrides any format setting defined in Excel.